﻿using System;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Collections.Generic;
using Dapper;
using API_Service.DBHelper;
using API_Service.BizLogic.BasicData;

namespace API_Service.BizLogic.WipLogApi
{
    public class SNControlService : BaseLogicService
    {
        private BomDataService _bomService = new BomDataService();
        public SNControlService() { }
        public ExecutionResult<dynamic> ScanData(string sn, string group, string line, string emp)
        {
            var result = new ExecutionResult<dynamic>();

            string model = string.Empty;

            string section = "Default";

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select serial_number,model_name from r_wip_tracking_t where serial_number=@sn";

                    conn.Open();

                    dynamic[] res = conn.Query(sql, new { sn = sn }).ToArray();

                    if (res.Length > 0)
                    {
                        result = this.Check_SN(group, sn);
                        if (!result.Status) return result;

                        model = result.Datas[0].sku_info.model_name;

                        result = this.Check_Route(group, sn);
                        if (!result.Status) return result;

                        result = this.PassStation(sn, model, emp, line, section, group);
                        if (!result.Status) return result;

                        result = this.UpdateWIPInfo(group, sn);
                        if (!result.Status) return result;

                        dynamic[] returnStrings = conn.Query("select a.input_qty,a.output_qty,a.target_qty,b.mo_number,b.model_name,b.sku_no " +
                                                                                       "from r_mo_base_t a,r_wip_tracking_t b where a.mo_no=b.mo_number and b.serial_number = @sn", 
                                                                                        new { sn = sn }).ToArray();

                        result.Datas = returnStrings;
                        result.Status = true;
                        result.Message = "SN：" + sn + "  过站完成.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "SN(序列号: " + sn + ")输入错误或者不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        public ExecutionResult<dynamic> Check_SN(string group, string sn)
        {
            var result = new ExecutionResult<dynamic>();

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select serial_number,mo_number,model_name,sku_no,bom_no from r_wip_tracking_t where serial_number=@sn";

                    conn.Open();

                    result.Datas = conn.Query(sql, new { sn = sn }).ToArray();

                    if (result.Datas.Length > 0)
                    {
                        result.Status = true;
                        result.Message = "OK";
                        var bom_no = result.Datas[0].bom_no;
                        var sku_infos = new { model_name = result.Datas[0].model_name, sku_no = result.Datas[0].sku_no };
                        List<object> lstReplyData = new List<object>();
                        lstReplyData.Add(new { sku_info = sku_infos });

                       var checkroute =  Check_Route(group, sn); //check route

                        result.Status = checkroute.Status;
                        result.Message = checkroute.Message;
                        //Added by jesse，按站别及BOM获取ESOP中的材料清单
                        if (result.Status)
                        {
                            var groupBomList = _bomService.QueryGroupKPNPickListInBom(group, bom_no);
                            result.Status = groupBomList.Status;
                            lstReplyData.Add(new { groupkpnlist = groupBomList.Datas });
                        }
                        result.Datas = lstReplyData.ToArray();
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "SN(序列号: " + sn + ")输入错误或者不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> Check_Route(string mygroup, string sn)
        {
            var result = new ExecutionResult<dynamic>();

            string current_group, next_group, mo_flag, current_flag, route_code;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string sql = "select w.serial_number,w.group_name,w.next_station,w.error_flag,m.route_code,m.mo_status " +
                                          "from r_wip_tracking_t w, r_mo_base_t m where w.mo_number = m.mo_no	and w.serial_number = @sn";

                    conn.Open();

                    dynamic[] res = conn.Query(sql, new { sn = sn }).ToArray();

                    if (res.Length > 0)
                    {
                        current_group = res[0].group_name;
                        next_group = res[0].next_station;
                        mo_flag = res[0].mo_status;
                        current_flag = res[0].error_flag;
                        route_code = res[0].route_code;

                        if (next_group == mygroup)
                        {
                            result.Message = "OK";
                            result.Status = true;
                            return result;
                        }
                        else
                        {
                            sql = "select group_next from c_route_control_t " +
                                              "where state_flag = @current_f and route_code = @r_code and group_name = @current_g " +
                                              "and group_next = @mygroup order by step_sequence";

                            res = conn.Query(sql, new
                            {
                                current_f = current_flag,
                                r_code = route_code,
                                current_g = current_group,
                                mygroup = mygroup
                            }).ToArray();

                            if (res.Length > 0)
                            {
                                result.Message = sn + " 流程检查OK";
                                result.Status = true;
                                return result;
                            }
                            else
                            {
                                sql = "select top 1 group_next from c_route_control_t where state_flag = @current_f " +
                                          "and route_code = @r_code and group_name = @current_g order by step_sequence";

                                res = conn.Query(sql, new
                                {
                                    current_f = current_flag,
                                    r_code = route_code,
                                    current_g = current_group

                                }).ToArray();


                                if (res.Length > 0)
                                {
                                    result.Status = false;
                                    result.Message = "流程错误，下一站" + res[0].group_next + "(" + current_flag + ")";
                                    return result;
                                }
                                else
                                {
                                    result.Message = "流程检查错误.";
                                    result.Status = false;
                                    return result;
                                }


                            }
                        }
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "SN(序列号: " + sn + ")输入错误或者不存在.";
                    }
                }
                catch (Exception ex)
                {
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> UpdateWIPInfo(string group, string sn)
        {
            var result = new ExecutionResult<dynamic>();

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string mo = sn.Substring(0, 11);

                    string sql = @"update r_wip_tracking_t  
                                                set  group_name=@group, station_name=@group + '1', in_station_time=getdate() 
                                                where serial_number=@sn";

                    conn.Open();

                    transaction = conn.BeginTransaction();

                    int res = conn.Execute(sql, new { sn = sn, group=group }, transaction);

                    if (res > 0)
                    {
                        //记录当前MO的投入或者产出数
                        var getMOinfo = conn.Query("select mo_no, input_group, output_group from r_mo_base_t where mo_no=@mo", new { mo = mo }, transaction).First();

                        if (getMOinfo.input_group == group)
                        {
                            conn.Execute("update r_mo_base_t  set  input_qty=input_qty+1 where mo_no=@mo", new { mo = mo }, transaction);
                        }
                        if (getMOinfo.output_group == group)
                        {
                            conn.Execute("update r_mo_base_t  set  output_qty=output_qty+1 where mo_no=@mo", new { mo = mo }, transaction);
                        }

                        //获取当前MO的目标和产出数
                        dynamic[] qtys = conn.Query("select target_qty, output_qty from r_mo_base_t where mo_no=@mo", new { mo = mo }, transaction).ToArray();

                        if (qtys[0].output_qty >= qtys[0].target_qty)
                        {
                            //更新MO状态
                            conn.Execute("update r_mo_base_t  set  mo_status='3' where mo_no=@mo", new { mo = mo }, transaction); //更新MO状态
                        }

                        //更新log
                        conn.Execute("insert r_wip_log_t (serial_number,line_name,group_name,station_name,in_station_time,error_flag,update_time,update_user) " +
                                             "select serial_number,line_name,group_name,station_name,in_station_time,error_flag," +
                                             "getdate(),update_user from  r_wip_tracking_t where serial_number=@sn", 
                                             new { sn = sn }, transaction);

                    }

                    transaction.Commit();//提交事务/

                    result.Status = true;
                    result.Message = "更新成功.";
                   
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }
        private ExecutionResult<dynamic> PassStation(string sn, string model, string emp, string line, string section, string group)
        {
            var result = new ExecutionResult<dynamic>();

            string date1 = string.Empty;
            string date2 = string.Empty;

            IDbTransaction transaction = null;

            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    conn.Open();

                    transaction = conn.BeginTransaction();

                    string datesql = @"select convert(varchar,getdate(),112) as date1,datepart( hh , getdate() )  as date2 ";

                    //获取当前时间戳
                    dynamic[] res = conn.Query(datesql, null, transaction).ToArray();

                    if (res.Length > 0)
                    {
                        date1 = res[0].date1.ToString();
                        date2 = res[0].date2.ToString();
                        date2 = Convert.ToInt32(date2) < 10 ? "0" + date2 : date2;
                    }

                    string sql_getinfo = " select * from r_wip_log_t wl where wl.serial_number =@sn and wl.group_name =@group ";

                    res = conn.Query(sql_getinfo, new { sn = sn, group = group }, transaction).ToArray();

                    if (res.Length > 0)
                    {
                        string sql1 = @"select * from r_station_rec_t
                                                  where line_name = @line  and section_name = @section
                                                  and work_date =  convert(varchar,getdate(),112)  and group_name = @mygroup
                                                  and mo_number = @mo and work_section=@work_section ";

                        //获取当前时间段的统计信息是否存在
                        res = conn.Query(sql1, new
                        {
                            line = line,
                            section = section,
                            mygroup = group,
                            mo = sn.Substring(0, 11),
                            work_section = date2
                        }, transaction).ToArray();

                        if (res.Length > 0)
                        {
                            string sql3 = @" update r_station_rec_t set repass_qty = repass_qty + 1, workdate_id=CONVERT(VARCHAR(24),GETDATE(),121) where line_name = @line
                                                            and section_name = @section  and work_date = @data  and group_name = @mygroup
                                                            and mo_number = @mo and work_section=@work_section";

                            int i = conn.Execute(sql3, new
                            {
                                line = line,
                                section = section,
                                mygroup = group,
                                mo = sn.Substring(0, 11),
                                work_section = date2
                            }, transaction);

                            if (i > 0)
                            {

                            }
                        }
                        else
                        {
                            string sql4 = @"  insert into r_station_rec_t (work_date, work_section, mo_number, line_name, group_name, section_name,
                                                        wip_qty, pass_qty, fail_qty, repass_qty, refail_qty, last_flag, model_name,workdate_id)
                                                        values (@date1,@date2, @mo, @line, @mygroup, @section, 0, 0, 0, 1, 0, '0', @model,CONVERT(VARCHAR(24),GETDATE(),121))";

                            int i = conn.Execute(sql4, new
                            {
                                date1 = date1,
                                date2 = date2,
                                mo = sn.Substring(0, 11),
                                line = line,
                                mygroup = group,
                                section = section,
                                model = model
                            }, transaction);

                            if (i > 0)
                            {

                            }

                        }
                    }
                    else
                    {
                        string sql5 = @"select * from r_station_rec_t where line_name = @line
                                                  and section_name = @section and work_date = @data and group_name = @mygroup
                                                  and mo_number = @mo and work_section=@work_section";
                        res = conn.Query(sql5, new
                        {
                            line = line,
                            section = section,
                            data = date1,
                            mygroup = group,
                            mo = sn.Substring(0, 11),
                            work_section = date2
                        }, transaction).ToArray();

                        if (res.Length > 0)
                        {
                            string sql6 = @" update r_station_rec_t set pass_qty = pass_qty + 1, workdate_id=CONVERT(VARCHAR(24),GETDATE(),121) where line_name = @line and section_name = @section
                                                       and work_date = @date and group_name = @mygroup and mo_number = @mo and work_section=@work_section ";

                            int i = conn.Execute(sql6, new
                            {
                                line = line,
                                section = section,
                                date = date1,
                                mygroup = group,
                                mo = sn.Substring(0, 11),
                                work_section = date2
                            }, transaction);
                        }
                        else
                        {
                            string sql7 = @"  insert into r_station_rec_t 
                                                 (work_date, work_section, mo_number, line_name, group_name, section_name, wip_qty, pass_qty, fail_qty, repass_qty, refail_qty, last_flag, model_name,workdate_id)
                                                   values (@data1, @data2,@mo, @line, @mygroup, @section, 0, 1, 0, 0, 0, '0', @model, CONVERT(VARCHAR(24),GETDATE(),121))";

                            int i = conn.Execute(sql7, new
                            {
                                data1 = date1,
                                data2 = date2,
                                mo = sn.Substring(0, 11),
                                line = line,
                                mygroup = group,
                                section = section,
                                model = model
                            }, transaction);

                        }
                    }

                    transaction.Commit();

                    result.Status = true;
                    result.Message = sn + " 过站更新成功.";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    Logger.WriteError(MethodBase.GetCurrentMethod(), ex);
                    result.Status = false;
                    result.Message = "Error: " + ex.Message;
                }
                finally
                {
                    conn.Close();
                }
            }

            return result;
        }

    }
}